package bcared;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Equipment {

	//actual columns in the equipment table
	private int equipmentID;
	private String serialNumber;
	private int locationID;
	private String room;
	private int statusID;
	private int productID;
	private String inServiceDate;
	private String outServiceDate;
	
	//useful information from other tables
	private String productName;
	private float retailPrice;
	private String oem;
	private String address1;
	private String address2;
	private String city;
	private String state;
	private String zip;
	private String phone;
	private String statusDesc;
	
	private ResultSet rs = null;
	private CallableStatement csmt = null;
	private PreparedStatement ps = null;
	private OraDB myDB = new OraDB();
	
	
	public Equipment (int equipmentID) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		this.equipmentID = equipmentID;
		populate();
	}

	public void populate() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "SELECT equipmentID, serialnumber, Equipment.locationid, room, Status.statusid, Equipment.productid, TO_CHAR(inservicedate, 'YYYY-MM-DD') as inservicedate , TO_CHAR(outservicedate, 'YYYY-MM-DD') as outservicedate,"
				+ " productName, retailPrice, oem, address1, address2, city, state, zip, phone, statusDescription"
				+ " FROM Equipment"
				+ " INNER JOIN Location ON Location.locationID = Equipment.locationID"
				+ " INNER JOIN Product ON Product.productID = Equipment.productID"
				+ " INNER JOIN Status ON Status.statusID = Equipment.statusID"
				+ " WHERE equipmentID = ?";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, this.equipmentID);
		rs = ps.executeQuery();
		
		// populate the local variables
		while (rs.next()) {
			this.equipmentID = rs.getInt("equipmentID");
			this.serialNumber = rs.getString("serialNumber");
			this.locationID = rs.getInt("locationID");
			this.room = rs.getString("room");
			this.statusID = rs.getInt("statusID");
			this.productID = rs.getInt("productID");
			this.inServiceDate = rs.getString("inServiceDate");
			this.outServiceDate = rs.getString("outServiceDate");

			this.productName = rs.getString("productName");
			this.retailPrice = rs.getFloat("retailPrice");
			this.oem = rs.getString("oem");
			this.address1 = rs.getString("address1");
			this.address2 = rs.getString("address2");
			this.city = rs.getString("city");
			this.state = rs.getString("state");
			this.zip = rs.getString("zip");
			this.phone = rs.getString("phone");
			this.statusDesc = rs.getString("statusDescription");
		}		

	}
	

	public int getEquipmentID() {
		return equipmentID;
	}

	public String getSerialNumber() {
		return serialNumber;
	}

	public int getLocationID() {
		return locationID;
	}

	public String getRoom() {
		return room;
	}

	public int getStatusID() {
		return statusID;
	}

	public int getProductID() {
		return productID;
	}

	public String getInServiceDate() {
		return inServiceDate;
	}

	public String getOutServiceDate() {
		if (this.outServiceDate == null) {
			return "N/A";
		} else {
			return outServiceDate;
		}
	}

	public String getProductName() {
		return productName;
	}

	public float getRetailPrice() {
		return retailPrice;
	}

	public String getOem() {
		return oem;
	}

	public String getAddress1() {
		return address1;
	}

	public String getAddress2() {
		return address2;
	}

	public String getCity() {
		return city;
	}

	public String getState() {
		return state;
	}

	public String getZip() {
		return zip;
	}

	public String getPhone() {
		return phone;
	}

	public ResultSet getRs() {
		return rs;
	}

	public CallableStatement getCsmt() {
		return csmt;
	}

	public PreparedStatement getPs() {
		return ps;
	}

	public OraDB getMyDB() {
		return myDB;
	}

	public String getStatusDesc() {
		return statusDesc;
	}

	public void setEquipmentID(int equipmentID) {
		this.equipmentID = equipmentID;
	}

	public void setSerialNumber(String serialNumber) {
		this.serialNumber = serialNumber;
	}

	public void setLocationID(int locationID) {
		this.locationID = locationID;
	}

	public void setRoom(String room) {
		this.room = room;
	}

	public void setStatusID(int statusID) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		this.statusID = statusID;
		
		myDB.createConnection();
		String myQuery = "UPDATE Equipment SET statusID = ? WHERE equipmentID = ?";
		
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, statusID);
		ps.setInt(2, this.equipmentID);
		rs = ps.executeQuery();
	}

	public void setProductID(int productID) {
		this.productID = productID;
	}

	public void setInServiceDate(String inServiceDate) {
		this.inServiceDate = inServiceDate;
	}

	public void setOutServiceDate(String outServiceDate) {
		this.outServiceDate = outServiceDate;
	}
	
	public void closeConnection() throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {
		myDB.closeConnection();
	}
	
}
